package com.mofeng.myshop.mapper;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.mofeng.myshop.domain.po.UserBase;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.mofeng.myshop.until.Constants;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * <p>
 * 用户基础信息 Mapper 接口
 * </p>
 *
 * @author MoFeng
 * @since 2024-04-17
 */
public interface UserBaseMapper extends BaseMapper<UserBase> {

    @Select("SELECT * FROM `tb_user_base` WHERE `email` = #{email}")
    UserBase findByEmail(@Param("email") String email);

    @Select("SELECT u.*,u.`id` uid ,p.name pname,p.id pid,p.number,r.name rname,r.id rid,i.* " +
            "FROM `tb_user_base` u " +
            "LEFT JOIN `tb_user_power` p ON u.`power_id` = p.`id` " +
            "LEFT JOIN `tb_user_role` r ON u.`role_id` = r.`id` " +
            "LEFT JOIN `tb_user_info` i ON u.`id` = i.`id` " +
            "WHERE " +
            "u.`name` = #{userName} " +
            "OR u.`phone` = #{userName} " +
            "OR u.`email` = #{userName} ")
    @ResultMap("BaseAllMsgResultMap")
    UserBase loginFindByUserName(@Param("userName") String userName);

    @Select("SELECT * FROM `tb_user_base` WHERE `phone` = #{phone}")
    UserBase findByPhone(@Param("phone") String phone);

    @Update("UPDATE `tb_user_base` SET `state` = " + Constants.State.DELETE_STATE + " WHERE `id` = #{userId}")
    void updateDelStateById(@Param("userId") Long userId);

    @Select("SELECT * FROM `tb_user_base` WHERE `id` = #{userId}")
    UserBase findById(@Param("userId") Long userId);

    @Select("SELECT u.* ,u.`id` uid,p.name pname,p.id pid,p.number,r.name rname,r.id rid,i.* " +
            "FROM `tb_user_base` u " +
            "LEFT JOIN `tb_user_power` p ON u.`power_id` = p.`id` " +
            "LEFT JOIN `tb_user_role` r ON u.`role_id` = r.`id` " +
            "LEFT JOIN `tb_user_info` i ON u.`id` = i.`id` " +
            "WHERE " +
            "u.`id` = #{userId}")
    @ResultMap("BaseAllMsgResultMap")
    UserBase findAllInfoById(@Param("userId") Long userId);


    @Select({"<script>",
            "SELECT ",
            "ub.*,ub.`id` uid,",
            "ui.*,ui.id AS infoId,",
            "up.id AS powerId,up.name AS power_name,up.number,ur.`name` AS role_name,",
            "ur.id AS roleId",
            "FROM `tb_user_base` ub",
            "LEFT JOIN `tb_user_info` ui ON ub.id = ui.id",
            "LEFT JOIN `tb_user_power` up ON ub.power_id = up.id",
            "LEFT JOIN `tb_user_role` ur ON ub.role_id = ur.id",
            "<where>",
            "<if test='key != null'>",
            "ub.`name` LIKE #{key} ",
            "OR ub.email LIKE #{key} ",
            "OR ui.game_name LIKE #{key} ",
            "OR ui.game_id LIKE #{key} ",
            "OR ui.steam_id LIKE #{key} ",
            "OR ui.qq LIKE #{key} ",
            "</if>",
            "<if test='roles != null'>",
            "AND ub.role_id = #{roles} ",
            "</if>",
            "<if test='powerId != null'>",
            "AND ub.power_id = #{powerId}",
            "</if>",
            "<if test='state != null'>",
            "AND `state` = #{state}",
            "</if>",
            "<if test='moneyMin != null'>",
            "AND ui.money &gt;= #{moneyMin}",
            "</if>",
            "<if test='moneyMax != null'>",
            "AND ui.money &lt;= #{moneyMax}",
            "</if>",
            "<if test='amountMin != null'>",
            "AND ui.amount &gt;= #{amountMin}",
            "</if>",
            "<if test='amountMax != null'>",
            "AND ui.amount &lt;= #{amountMax}",
            "</if>",
            "</where>",
            "</script>"})
    @ResultMap("BaseAllMsgResultMap")
    Page<UserBase> findList(@Param("key") String key, @Param("roles") String roles, @Param("powerId") String powerId,
                            @Param("moneyMin") Long moneyMin, @Param("moneyMax") Long moneyMax,
                            @Param("amountMin") Long amountMin, @Param("amountMax") Long amountMax, @Param("state") String State,
                            Page<UserBase> userBasePage);

    @Select({"<script>",
            "SELECT * FROM `tb_user_base` ",
            "<where>",
            "<if test='state != null'>",
            "AND `state` = #{state}",
            "</if>",
            "</where>",
            "</script>"})
    List<UserBase> findAll(@Param("state") String State);

    @Select({"<script>",
            "SELECT * FROM `tb_user_base` WHERE ",
            "`id` IN ",
            "<foreach item='id' collection='userIds' open='(' separator=',' close=' )'>",
            " #{id} ",
            "</foreach>",
            "</script>"})
    List<UserBase> findAllById(@Param("userIds") List<String> userIds);

    @Select("SELECT u.* ,u.`id` uid,p.name pname,p.id pid,p.number,r.name rname,r.id rid,i.* " +
            "FROM `tb_user_base` u " +
            "LEFT JOIN `tb_user_power` p ON u.`power_id` = p.`id` " +
            "LEFT JOIN `tb_user_role` r ON u.`role_id` = r.`id` " +
            "LEFT JOIN `tb_user_info` i ON u.`id` = i.`id` " +
            "WHERE " +
            "i.`invitation_code` = #{invitationCode}")
    @ResultMap("BaseAllMsgResultMap")
    UserBase findByIdInvitationCode(@Param("invitationCode") String invitationCode);

    @Select("SELECT u.* ,u.`id` uid,p.name pname,p.id pid,p.number,r.name rname,r.id rid,i.* " +
            "FROM `tb_user_base` u " +
            "LEFT JOIN `tb_user_power` p ON u.`power_id` = p.`id` " +
            "LEFT JOIN `tb_user_role` r ON u.`role_id` = r.`id` " +
            "LEFT JOIN `tb_user_info` i ON u.`id` = i.`id` " +
            "WHERE u.`state` = #{state}" +
            "ORDER BY CAST(i.`money` AS DOUBLE) DESC LIMIT  #{size}")
    @ResultMap("BaseAllMsgResultMap")
    List<UserBase> amountRanking(@Param("size") int size, @Param("state") String state);
}
